﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Common;
using System.Data.SqlClient;
using System.Data;
using WFNetLib.ADO;

namespace DAL
{
    public class unit_infoDal
    {
        public unit_infoData GetUnitDetail(string unit_number)
        {
            SqlDataReader Rd = null;
            List<unit_infoData> list = new List<unit_infoData>();
            unit_infoData model = new unit_infoData();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM unit_info where unit_number='" + unit_number + "'");
            SqlParameter[] parameters = {
				new SqlParameter("@unit_number", SqlDbType.VarChar,10)
                                        };
            parameters[0].Value = unit_number;
            Rd = SQLServerOP.ReaderExecuteSQL(strSql.ToString(), parameters);
            if (Rd.Read())
            {
                model.unit_number = Rd["unit_number"].ToString();
                model.unit_shelf_number = Rd["unit_shelf_number"].ToString();
                model.phone1 = Rd["phone1"].ToString();
                model.phone2 = Rd["phone2"].ToString();
                model.phone3 = Rd["phone3"].ToString();
                model.create_user_id = int.Parse(Rd["create_user_id"].ToString());
                model.create_timestamp = DateTime.Parse(Rd["create_timestamp"].ToString());
                model.update_user_id = Rd["update_user_id"].ToString() == "" ? 1 : int.Parse(Rd["update_user_id"].ToString());
                model.update_timestamp = Rd["update_timestamp"].ToString() == "" ? DateTime.Now : DateTime.Parse(Rd["update_timestamp"].ToString());
            }
            Rd.Close();
            Rd.Dispose();
            return model;
        }
        public int AddUnitInfo(unit_infoData model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("IF NOT EXISTS (SELECT * FROM unit_info WHERE unit_number = '" + model.unit_number + "') ");
            strSql.Append("insert into unit_info(");
            strSql.Append("unit_number,unit_shelf_number,phone1,phone2,phone3,create_user_id,create_timestamp,update_user_id,update_timestamp)");
            strSql.Append(" values (");
            strSql.Append("@unit_number,@unit_shelf_number,@phone1,@phone2,@phone3,@create_user_id,@create_timestamp,@update_user_id,@update_timestamp)");
            //strSql.Append(";select @@IDENTITY ");
            strSql.Append(" ELSE ");
            strSql.Append("update unit_info set ");
            strSql.Append("unit_shelf_number=@unit_shelf_number,");
            strSql.Append("phone1=@phone1,");
            strSql.Append("phone2=@phone2,");
            strSql.Append("phone3=@phone3,");
            strSql.Append("create_user_id=@create_user_id,");
            strSql.Append("create_timestamp=@create_timestamp,");
            strSql.Append("update_user_id=@update_user_id,");
            strSql.Append("update_timestamp=@update_timestamp");
            strSql.Append(" where unit_number=@unit_number ");
            SqlParameter[] parameters = {
                    new SqlParameter("@unit_shelf_number", SqlDbType.VarChar,20),
                    new SqlParameter("@phone1", SqlDbType.VarChar,15),
                    new SqlParameter("@phone2", SqlDbType.VarChar,15),
                    new SqlParameter("@phone3", SqlDbType.VarChar,15),
                    new SqlParameter("@create_user_id", SqlDbType.Int,4),
                    new SqlParameter("@create_timestamp", SqlDbType.DateTime),
                    new SqlParameter("@update_user_id", SqlDbType.Int,4),
                    new SqlParameter("@update_timestamp", SqlDbType.DateTime),
                    new SqlParameter("@unit_number", SqlDbType.VarChar,10)  
                                        };
            parameters[0].Value = model.unit_shelf_number;
            parameters[1].Value = model.phone1;
            parameters[2].Value = model.phone2;
            parameters[3].Value = model.phone3;
            parameters[4].Value = model.create_user_id;
            parameters[5].Value = model.create_timestamp;
            parameters[6].Value = model.update_user_id;
            parameters[7].Value = model.update_timestamp;
            parameters[8].Value = model.unit_number;
            object obj = SQLServerOP.ScalarSQL(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        public int UpdateUnitInfo(unit_infoData model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update unit_info set ");
            strSql.Append("unit_shelf_number=@unit_shelf_number,");
            strSql.Append("phone1=@phone1,");
            strSql.Append("phone2=@phone2,");
            strSql.Append("phone3=@phone3,");
            strSql.Append("update_user_id=@update_user_id,");
            strSql.Append("update_timestamp=@update_timestamp");
            strSql.Append(" where unit_number=@unit_number ");
            SqlParameter[] parameters = {
                    new SqlParameter("@unit_shelf_number", SqlDbType.VarChar,20),
                    new SqlParameter("@phone1", SqlDbType.VarChar,15),
                    new SqlParameter("@phone2", SqlDbType.VarChar,15),
                    new SqlParameter("@phone3", SqlDbType.VarChar,15),
                    new SqlParameter("@update_user_id", SqlDbType.Int,4),
                    new SqlParameter("@update_timestamp", SqlDbType.DateTime),
                    new SqlParameter("@unit_number", SqlDbType.VarChar,10)                                                                
                                        };
            parameters[0].Value = model.unit_shelf_number;
            parameters[1].Value = model.phone1;
            parameters[2].Value = model.phone2;
            parameters[3].Value = model.phone3;
            parameters[4].Value = model.update_user_id;
            parameters[5].Value = model.update_timestamp;
            parameters[6].Value = model.unit_number;
            return SQLServerOP.NonQuerySQL(strSql.ToString(), parameters);
        }
    }
}
